package models;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DAO extends SQLiteOpenHelper {
	private static String TAG = "TAG";
	private static String DB_PATH = "/data/data/com.waiter.agent/databases/";
	private static String DB_NAME = "WaiterAgent.sqlite";
	private static String TABLE_PHIEUGOIMON = "PHIEUGOIMON";
	private static String TABLE_MONAN = "MONAN";
	private static String TABLE_LOAIMONAN = "LOAIMONAN";
	private static String TABLE_ChiTietPGM = "CTPGM";
	private SQLiteDatabase mDataBase; 
	private final Context mContext;

	public DAO(Context context) 
	{
	    super(context, DB_NAME, null, 1);
	    DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
	    this.mContext = context;
	    
	}   

	public void createDataBase() throws IOException
	{
	    boolean mDataBaseExist = checkDataBase();
	    if(!mDataBaseExist)
	    {
	        this.getReadableDatabase();
	        try 
	        {
	            copyDataBase();
	        } 
	        catch (IOException mIOException) 
	        {
	            throw new Error("ErrorCopyingDataBase");
	        }
	    }
	}

	private boolean checkDataBase()
	{
	    SQLiteDatabase mCheckDataBase = null;
	    try
	    {
	        String myPath = DB_PATH + DB_NAME;
	        mCheckDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
	    }
	    catch(SQLiteException mSQLiteException)
	    {
	        Log.e(TAG, "DatabaseNotFound " + mSQLiteException.toString());
	    }

	    if(mCheckDataBase != null)
	    {
	        mCheckDataBase.close();
	    }
	    return mCheckDataBase != null;
	}

	private void copyDataBase() throws IOException
	{
	    InputStream mInput = mContext.getAssets().open(DB_NAME);
	    String outFileName = DB_PATH + DB_NAME;
	    OutputStream mOutput = new FileOutputStream(outFileName);
	    byte[] mBuffer = new byte[1024];
	    int mLength;
	    while ((mLength = mInput.read(mBuffer))>0)
	    {
	        mOutput.write(mBuffer, 0, mLength);
	    }
	    mOutput.flush();
	    mOutput.close();
	    mInput.close();
	}

	public boolean openDataBase() throws SQLException
	{
	    String mPath = DB_PATH + DB_NAME;
	    mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
	    return mDataBase != null;
	}

	@Override
	public synchronized void close() 
	{
	    if(mDataBase != null)
	        mDataBase.close();
	    super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) 
	{ }

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
	{
	    Log.v(TAG, "UpgradingDatabase, This will drop current database and will recreate it");
	}

	
	//////////Get functions//////////
	public List<DTO_PhieuGoiMon> LayDSPhieuGoiMon (){
		List<DTO_PhieuGoiMon> DSPGM = new ArrayList<DTO_PhieuGoiMon>();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_PHIEUGOIMON, null);
		c.moveToFirst();
		int numofRecord = c.getCount();
		for(int i=0;i<numofRecord;i++){
			DTO_PhieuGoiMon newPGM = new DTO_PhieuGoiMon();
			newPGM.setMaPGM(c.getInt(c.getColumnIndex("MaPGM")));
			String dateString = c.getString(c.getColumnIndex("Ngay"));
			Date d = Date.valueOf(dateString);
			newPGM.setNgay(d);
			newPGM.setDSMon(c.getString(c.getColumnIndex("DSMon")));
			newPGM.setSoban(c.getInt(c.getColumnIndex("SoBan")));
			newPGM.setTenKH(c.getString(c.getColumnIndex("TenKH")));
			String timeString = c.getString(c.getColumnIndex("ThoiGian"));
			Date t = Date.valueOf(timeString);
			newPGM.setThoigian(t);
			DSPGM.add(newPGM);
		}
		return DSPGM;
	}
	public DTO_PhieuGoiMon LayPhieuGoiMon(int maPGM){
		DTO_PhieuGoiMon pgm = new DTO_PhieuGoiMon();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_PHIEUGOIMON+" where MaPGM="+String.valueOf(maPGM), null);
		c.moveToFirst();
		pgm.setMaPGM(c.getInt(c.getColumnIndex("MaPGM")));
		String dateString = c.getString(c.getColumnIndex("Ngay"));
		Date d = Date.valueOf(dateString);
		pgm.setNgay(d);
		pgm.setDSMon(c.getString(c.getColumnIndex("DSMon")));
		pgm.setSoban(c.getInt(c.getColumnIndex("SoBan")));
		pgm.setTenKH(c.getString(c.getColumnIndex("TenKH")));
		String timeString = c.getString(c.getColumnIndex("ThoiGian"));
		Date t = Date.valueOf(timeString);
		pgm.setThoigian(t);
		return pgm;
	}
	
	public List<DTO_MonAn> LayDSMonAn (){
		List<DTO_MonAn> DSMonAn = new ArrayList<DTO_MonAn>();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_MONAN, null);
		c.moveToFirst();
		int numofRecord = c.getCount();
		for(int i=0;i<numofRecord;i++){
			DTO_MonAn newMonAn = new DTO_MonAn();
			newMonAn.setMaMon(c.getInt(c.getColumnIndex("MaMon")));
			newMonAn.setTenMon(c.getString(c.getColumnIndex("TenMon")));
			newMonAn.setMaLoaiM(c.getInt(c.getColumnIndex("MaLoaiM")));
			DSMonAn.add(newMonAn);
			
		}
		return DSMonAn;
	}
	
	public DTO_MonAn LayMonAn(int maMon){
		DTO_MonAn ma = new DTO_MonAn();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_MONAN+" where maMonAn="+String.valueOf(maMon), null);
		c.moveToFirst();
		if(c.getCount()<1)
			return null;
		ma.setMaMon(maMon);
		ma.setTenMon(c.getString(c.getColumnIndex("TenMon")));
		ma.setMaLoaiM(c.getInt(c.getColumnIndex("MaLoaiM")));
		return ma;
	
	}
	
	public List<DTO_CTPGM> LayDSCTPGM (int maPGM){
		List<DTO_CTPGM> DSPGM = new ArrayList<DTO_CTPGM>();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_ChiTietPGM+ " where maPGM="+String.valueOf(maPGM), null);
		c.moveToFirst();
		int numofRecord = c.getCount();
		for(int i=0;i<numofRecord;i++){
			DTO_CTPGM newCTPGM = new DTO_CTPGM();
			newCTPGM.setMaMon(c.getInt(c.getColumnIndex("maMon")));
			newCTPGM.setMaPGM(maPGM);
			newCTPGM.setSoluong(c.getInt(c.getColumnIndex("SoLuong")));
			DSPGM.add(newCTPGM);
		}
		return DSPGM;
	}
	
	public DTO_CTPGM LayCTPGM(int maPGM, int maMon){
		DTO_CTPGM ctpgm = new DTO_CTPGM();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_ChiTietPGM+" where maPGM="+String.valueOf(maPGM)+" and maMon="+String.valueOf(maMon), null);
		if(c.getCount()<1)
			return null;
		c.moveToFirst();
		ctpgm.setMaMon(maMon);
		ctpgm.setMaPGM(maPGM);
		ctpgm.setSoluong(c.getInt(c.getColumnIndex("SoLuong")));
		return ctpgm;
	}
	
	public List<DTO_LoaiMonAn> LayDSLoaiMonAn (){
		List<DTO_LoaiMonAn> DSLoaiMonAn = new ArrayList<DTO_LoaiMonAn>();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_LOAIMONAN, null);
		c.moveToFirst();
		int numofRecord = c.getCount();
		for(int i=0;i<numofRecord;i++){
			DTO_LoaiMonAn newLoaiMonAn = new DTO_LoaiMonAn();
			newLoaiMonAn.setMaLoaiM(c.getInt(c.getColumnIndex("MaLoaiM")));
			newLoaiMonAn.setTenloaiM(c.getString(c.getColumnIndex("TenLoaiM")));
			DSLoaiMonAn.add(newLoaiMonAn);			
		}
		return DSLoaiMonAn;
	}
	
	public DTO_LoaiMonAn LayLoaiMonAn(int maLoaiMon){
		DTO_LoaiMonAn loaiMA = new DTO_LoaiMonAn();
		Cursor c = mDataBase.rawQuery("select * from "+TABLE_LOAIMONAN+" where MaLoaiM="+String.valueOf(maLoaiMon), null);
		c.moveToFirst();
		if(c.getCount()<1)
			return null;
		loaiMA.setMaLoaiM(maLoaiMon);
		loaiMA.setTenloaiM(c.getString(c.getColumnIndex("TenLoaiM")));
		return loaiMA;
	}
	
	
//////////Add functions//////////
	
	//DSMON truyen theo kieu nhu sau:
	//<MaMon1>,<MaMon2>,<MaMon3>
	//vÃ­ dá»¥ phiáº¿u gá»�i mÃ³n cÃ³ 3 mÃ³n vá»›i mÃ£ mÃ³n láº§n lÆ°á»£t lÃ  1,2,3 thÃ¬ chuá»—i DSMON lÃ  "1,2,3"
	public boolean ThemPGM(int SoBan, Date Ngay, Date thoigian, String TenKH, String dsMon){
		ContentValues newRecord = new ContentValues();
		newRecord.put("SoBan", SoBan);
		SimpleDateFormat dateformat = new SimpleDateFormat("dd-MM-yyyy");
		SimpleDateFormat timeformat = new SimpleDateFormat("hh:mm");
		newRecord.put("Ngay",dateformat.format(Ngay));
		newRecord.put("Thoigian",dateformat.format(thoigian) );
		newRecord.put("TenKH",TenKH );
		newRecord.put("DSMon", dsMon);
		long result = mDataBase.insert(TABLE_PHIEUGOIMON, null, newRecord);
		if(result!=-1)
			return true;
		return false;
	}
	public boolean ThemCTPGM(int MaPGM, int MaMon, int Soluong ){
		ContentValues newRecord = new ContentValues();
		newRecord.put("MaPGM", MaPGM);
		newRecord.put("MaMon",MaMon );
		newRecord.put("Soluong", Soluong);
		long result = mDataBase.insert(TABLE_ChiTietPGM, null, newRecord);
		if(result!=-1)
			return true;
		return false;
	}
	public boolean ThemMonAn(String TenMon, int MaLoaiM){
		ContentValues newRecord = new ContentValues();
		newRecord.put("TenMon",TenMon );
		newRecord.put("MaLoaiM", MaLoaiM);
		long result = mDataBase.insert(TABLE_MONAN, null, newRecord);
		if(result!=-1)
			return true;	
		return false;
	}
	
	public boolean ThemLoaiMonAn(String TenLoaiM){
		ContentValues newRecord = new ContentValues();
		newRecord.put("TenLoaiM",TenLoaiM );
		long result = mDataBase.insert(TABLE_LOAIMONAN, null, newRecord);
		if(result!=-1)
			return true;	
		return false;
	}
	
//////////Update functions//////////
	
	public boolean CapNhatPhieuGoiMon(int maPGM, int SoBan, Date Ngay, Date thoigian, String TenKH, String dsMon){
		ContentValues newRecord = new ContentValues();
		newRecord.put("MaPGM", maPGM);
		newRecord.put("SoBan", SoBan);
		SimpleDateFormat dateformat = new SimpleDateFormat("dd-MM-yyyy");
		SimpleDateFormat timeformat = new SimpleDateFormat("hh:mm");
		newRecord.put("Ngay",dateformat.format(Ngay));
		newRecord.put("Thoigian",dateformat.format(thoigian) );
		newRecord.put("TenKH",TenKH );
		newRecord.put("DSMon", dsMon);
		int result = mDataBase.update(TABLE_PHIEUGOIMON, newRecord, "MaPGM="+String.valueOf(maPGM), null);
		if(result==1)
			return true;
		return false;
	}
	
	public boolean CapNhatCTPGM(int MaPGM, int MaMon, int Soluong ){
		ContentValues newRecord = new ContentValues();
		newRecord.put("MaPGM", MaPGM);
		newRecord.put("MaMon",MaMon );
		newRecord.put("Soluong", Soluong);
		int result = mDataBase.update(TABLE_ChiTietPGM, newRecord, "MaPGM="+String.valueOf(MaPGM)+" and MaMon="+String.valueOf(MaMon), null);
		if(result==1)
			return true;
		return false;
	}
	public boolean CapNhatMonAn(int MaMon, String TenMon, int MaLoaiM){
		ContentValues newRecord = new ContentValues();
		newRecord.put("MaMon", MaMon);
		newRecord.put("TenMon",TenMon );
		newRecord.put("MaLoaiM", MaLoaiM);
		int result = mDataBase.update(TABLE_MONAN, newRecord, "MaMon="+String.valueOf(MaMon), null);
		if(result==1)
			return true;	
		return false;
	}
	
	public boolean CapNhatLoaiMonAn(int MaLoaiM, String TenLoaiM){
		ContentValues newRecord = new ContentValues();
		newRecord.put("MaLoaiM", MaLoaiM);
		newRecord.put("TenLoaiM",TenLoaiM );
		int result = mDataBase.update(TABLE_LOAIMONAN, newRecord, "MaLoaiM="+String.valueOf(MaLoaiM), null);
		if(result==1)
			return true;	
		return false;
	}
	
//////////Delete functions//////////
	
	public boolean XoaPGM(int maPGM){
		int result = mDataBase.delete(TABLE_PHIEUGOIMON, "MaPGM="+String.valueOf(maPGM), null);
		if(result==1)
			return true;
		return false;
	}
	
	public boolean XoaCTPGM(int maPGM, int maMon){
		int result = mDataBase.delete(TABLE_ChiTietPGM, "MaPGM="+String.valueOf(maPGM)+" and MaMon="+String.valueOf(maMon), null);
		if(result==1)
			return true;
		return false;
	}
	
	public boolean XoaMonAn(int maMon){
		int result = mDataBase.delete(TABLE_MONAN, "MaMon="+String.valueOf(maMon), null);
		if(result==1)
			return true;
		return false;
	}
	
	public boolean XoaLoaiMonAn(int maLoaiM){
		int result = mDataBase.delete(TABLE_LOAIMONAN, "MaLoaiM="+String.valueOf(maLoaiM), null);
		if(result==1)
			return true;
		return false;
	}
}
